--    Program: 		CAPAREXT_RECEIVABLE_ACTIVITY.sql
--    Description:	Extract AR Receivable Activity information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(100);   
   l_set_of_books_id		number;
   
   cursor cur_system_option (p_set_of_books_id number) is
      select * from AR_RECEIVABLES_TRX_ALL
	  where set_of_books_id = p_set_of_books_id;

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_RECEIVABLE_ACTIVITY.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
       --raise;
   end;      

   for cur in cur_system_option(l_set_of_books_id) loop   	
     -- output data
     utl_file.put_line( l_utl_file, 
	   cur.receivables_trx_id|| chr(9) ||
	   cur.last_updated_by|| chr(9) ||
	   cur.last_update_date || chr(9) ||
	   cur.last_update_login|| chr(9) ||
	   cur.creation_date || chr(9) ||
	   cur.created_by|| chr(9) ||
	   cur.name|| chr(9) ||
	   cur.code_combination_id|| chr(9) ||
	   cur.set_of_books_id|| chr(9) ||
	   cur.description|| chr(9) ||
	   cur.status|| chr(9) ||
	   cur.type|| chr(9) ||
	   cur.start_date_active|| chr(9) ||
	   cur.end_date_active|| chr(9) ||
	   cur.default_acctg_distribution_set|| chr(9) ||
	   cur.inactive_date|| chr(9) ||   
	   cur.attribute_category|| chr(9) ||
	   cur.attribute1|| chr(9) ||
	   cur.attribute2|| chr(9) ||
	   cur.attribute3|| chr(9) ||
	   cur.attribute4|| chr(9) ||
	   cur.attribute5|| chr(9) ||
	   cur.attribute6|| chr(9) ||
	   cur.attribute7|| chr(9) ||
	   cur.attribute8|| chr(9) ||
	   cur.attribute9|| chr(9) ||
	   cur.attribute10|| chr(9) ||
	   cur.attribute11|| chr(9) ||
	   cur.attribute12|| chr(9) ||
	   cur.attribute13|| chr(9) ||
	   cur.attribute14|| chr(9) ||
	   cur.attribute15|| chr(9) ||
	   cur.org_id|| chr(9) ||
	   cur.global_attribute_category|| chr(9) ||
	   cur.global_attribute1|| chr(9) ||
	   cur.global_attribute2|| chr(9) ||
	   cur.global_attribute3|| chr(9) ||
	   cur.global_attribute4|| chr(9) ||
	   cur.global_attribute5|| chr(9) ||
	   cur.global_attribute6|| chr(9) ||
	   cur.global_attribute7|| chr(9) ||
	   cur.global_attribute8|| chr(9) ||
	   cur.global_attribute9|| chr(9) ||
	   cur.global_attribute10|| chr(9) ||
	   cur.global_attribute11|| chr(9) ||
	   cur.global_attribute12|| chr(9) ||
	   cur.global_attribute13|| chr(9) ||
	   cur.global_attribute14|| chr(9) ||
	   cur.global_attribute15|| chr(9) ||
	   cur.global_attribute16|| chr(9) ||
	   cur.global_attribute17|| chr(9) ||
	   cur.global_attribute18|| chr(9) ||
	   cur.global_attribute19|| chr(9) ||
	   cur.global_attribute20|| chr(9) ||
	   cur.asset_tax_code|| chr(9) ||
	   cur.liability_tax_code|| chr(9) ||
	   cur.gl_account_source|| chr(9) ||
	   cur.tax_code_source|| chr(9) ||
	   cur.tax_recoverable_flag|| chr(9) ||
	   cur.accounting_affect_flag|| chr(9) ||
	   cur.risk_elimination_days
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Receivable Activity not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_RECEIVABLE_ACTIVITY', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
